/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package lbim.server.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import static lbim.server.main.MapService.COLORS;
import static lbim.server.main.MapService.markerSeparator;

/**
 *
 * @author Jestan
 */
public class AccountManager {
    private Connection connection;
    public static String dbURL = "jdbc:derby:C:/Users/thurupathan/Desktop/LbImDB;create=true";
    public static String accoutTable = "CREATE TABLE USERS ( USERID VARCHAR(12) PRIMARY KEY, PASSWORD VARCHAR(12), LOCATION VARCHAR(50), STATUS  VARCHAR(25))";
    public AccountManager() throws SQLException, ClassNotFoundException {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        this.connection = DriverManager.getConnection(dbURL);
        //add shutdown hook
        Runnable runnable = new Runnable() {
            public void run() {
                    boolean gotSQLExc = false;
                    try {
                        DriverManager.getConnection("jdbc:derby:;shutdown=true");
                    } catch (SQLException se) {
                        if (se.getSQLState().equals("XJ015")) {
                            gotSQLExc = true;
                        }
                    }
                    if (!gotSQLExc) {
                        System.out.println("Database did not shut down normally");
                    } else {
                        System.out.println("Database shut down normally");
                    }

                }
        };
        Runtime.getRuntime().addShutdownHook(new Thread(runnable));
    }

    public void createTables() throws SQLException {
        Statement statement = connection.createStatement();
        statement.executeUpdate(accoutTable);
        connection.commit();
    }

    public void addUser(String username, String password) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("INSERT INTO USERS ( USERID, PASSWORD, STATUS ) VALUES( ?, ?, ? )");
        ps.setString(1, username);
        ps.setString(2, password);
        ps.setString(3, "OFFLINE");
        ps.executeUpdate();
    }

    public void updateLocation(String username, String location, String status) throws SQLException {
        Statement statement = connection.createStatement();
        statement.executeUpdate("UPDATE USERS SET LOCATION = '"+location+"', STATUS = '"+status+"' WHERE USERID = '"+username+"'");
    }

    public void updateStatus(String username, String status) throws SQLException {
        Statement statement = connection.createStatement();
        statement.executeUpdate("UPDATE USERS SET STATUS = '" + status + "' WHERE USERID = '" + username + "'");
    }

    public String getMarkers() throws SQLException {
        int colorIndex = 0;
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT USERID, LOCATION FROM USERS");
        StringBuffer markers = new StringBuffer();
        while(resultSet.next()) {
               markers.append(resultSet.getString(2))
                       .append(',')
                       .append(COLORS[colorIndex])
                       .append(resultSet.getString(1)
                       .charAt(0))
                       .append(markerSeparator);
                colorIndex++;
                if (colorIndex > 7) {
                    colorIndex = 0;
                }
        }

        markers.deleteCharAt(markers.length() -1);
        return markers.toString();

    }

    public ArrayList<String> getContactList(String userName) throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT USERID FROM USERS WHERE USERID != '"+userName+"'");
        ArrayList<String> userLocations = new ArrayList<String>();
        while (resultSet.next()) {
            userLocations.add(resultSet.getString(1));
        }

        return userLocations;
    }

    public ArrayList<String> getPresenceList(String userName) throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT USERID, STATUS FROM USERS WHERE USERID != '"+userName+"'");
        ArrayList<String> presenceList = new ArrayList<String>();
        while (resultSet.next()) {
            presenceList.add(resultSet.getString(1)+":"+resultSet.getString(2));
        }

        return presenceList;
    }

    public String getPresence(String userName) throws SQLException {
        ArrayList<String> contactList = getPresenceList(userName);
        StringBuffer cnBuffer = new StringBuffer();
        for (String contact : contactList) {
            cnBuffer.append(contact).append(',');
        }

        cnBuffer.deleteCharAt(cnBuffer.lastIndexOf(","));

        return cnBuffer.toString();
    }

    public String getContacts(String userName) throws SQLException {
        ArrayList<String> contactList = getContactList(userName);
        StringBuffer cnBuffer = new StringBuffer();
        for(String contact: contactList) {
            cnBuffer.append(contact)
                    .append(',');
        }

        cnBuffer.deleteCharAt(cnBuffer.lastIndexOf(","));

        return cnBuffer.toString();
    }

    public boolean isValidAccount(String username, String password) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("SELECT USERID, PASSWORD FROM USERS WHERE USERID = ?");
        ps.setString(1, username);
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()) {
            return resultSet.getString("PASSWORD").equals(password);
        } else {
            return false;
        }
    }

}
